GoogleスプレッドシートをデータソースとするBigQuery外部テーブルをCLIで作成する

Googleスプレッドシート上のデータをSQLでごにょごにょしたり、他のテーブルとJOINしてごにょごにょしたりしたくないですか？　ぼくはしたいです。さらば vlookup()

#Google BigQuery

渡辺聖剛

2021.09.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

TL;DR

テーブル定義ファイルをJSONで作る
bq mk --external_table_definition

BigQueryの外部テーブル

Google Cloudが持つデータ分析基盤のひとつ BigQuery (BQ) には、BQに格納されていないデータ¹を直接テーブルとして扱う「外部テーブル」という機能があります。

外部テーブルとして扱うことが可能なデータソースはいくつかありますが、個人的に嬉しいのはそれにGoogleスプレッドシートが含まれること。
つまり、スプレッドシートのデータをラフなままSQLで検索したり、他のBQ上のテーブルとJOINしたりすることが可能になるということです。

ということで、ちょっとやってみました。
外部テーブルの作成はGoogle Cloudのコンソール（Web UI）からポチポチするだけでできるのでそれでいいんですが、何回も繰り返すのは大変なので、ここではCLIからやってみます。

実行環境

コマンドラインツール

BQをCLIから扱うためには、bq コマンドラインツールが必要です。

このドキュメントにも書いてあるとおり、一番楽なのは「ブラウザのGoogle Cloudコンソールを開いて該当プロジェクトに移動してCloud Shell上から実行」することなので、以下そのつもりで記載します。

BQのデータセット

外部テーブルは必ず「データセット」の中に作る必要があるので、なければまずはそれを作っておきます。
下記のブログなどを参考にして下さい。

Googleスプレッドシート向けテーブル定義ファイル

まずはテーブル定義ファイル（table definition file）をJSONで用意します。

外部データソースに対するテーブル定義ファイルの作成 | BigQuery | Google Cloud

ドキュメントには bq mkdef コマンドを使ってひな形を作っていい感じに修正を〜と書かれてますが、そうした結果はだいたいこんな感じになります。

{
  "sourceFormat":"GOOGLE_SHEETS",
  "sourceUris":[
    "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXX/edit#gid=0"
  ],
  "googleSheetsOptions":{
    "range":"<シート名>!XX:XX",
    "skipLeadingRows":1
  },
  "schema":{
    "fields":[
      {"name":"name",    "type":"STRING", "mode":"NULLABLE"},
      {"name":"url",     "type":"STRING", "mode":"NULLABLE"},
      {"name":"id",      "type":"STRING", "mode":"NULLABLE"},
        ：
      {"name":"status",  "type":"STRING", "mode":"NULLABLE"}
    ]
  }
}

この内容でファイルを作成します。ファイル名はなんでもいいですが、例えば definition.json などとしておいてください。
ここではCloud Shell上で使う前提なので、何かしらの方法でCloud Shellの環境の中にファイルを作っておいてください。

ちなみにこのファイルは、可読性や説明のためにインデントしたり桁をそろえたりしてますが、JSONフォーマットとして正しければキーの順番や書式は問われません。

内容について簡単に解説します：

sourceFormat

Googleスプレッドシートを使う時には、ここに必ず GOOGLE_SHEETS と指定します。

sourceUris

外部データソースとするGoogleスプレッドシートのURL（正確には URI）を指定します。配列なのでお間違いなく。
URI の取得方法はこちらのドキュメントを参照ください。簡単に言えば「スプシ開いてブラウザからURLをコピペ」でOKです。

googleSheetsOptions.range

外部データソースとするスプレッドシートの範囲を指定します。もしシート全体が対象ならキーごと削除で構いません。

表記としては、Excelやスプレッドシートでもおなじみの シート名!セル範囲 です。例えば「『台帳』シートのB列の3行目〜G列の末尾まで全部」としたければ、下記の様になります。

    :
    "range":"台帳!B3:G",
    :

googleSheetsOptions.skipLeadingRows

range で指定された範囲（あるいはシート全体）のうち、ヘッダとして何行スキップするか、という指定です。例えば前の例で言えば、「実は3行目はタイトル行で、実データは4行目から」というのであれば、ここに「1」を指定します²。

なお、googleSheetsOptions の詳細については下記ドキュメントもご参照ください。

GoogleSheetsOptions - REST Resource: tables | BigQuery | Google Cloud

schema.fields

ここに各カラムのスキーマ情報を書いていきます。配列なので、上記の例でいえばB列、C列 ... の順に指定します。

nameはともかく、typeとmodeはそれぞれSTRING(文字列)、NULLABLE(空欄を許容)でいい気がします。ちなみに、STRINGとNULLABLE以外に何があるかについては、下記をご参照下さい。

標準 SQL データ型 - スキーマの指定 | BigQuery | Google Cloud

STRING と指定されているカラムのデータの実体が数値であった場合でも、SQL上で文字列 --> 数値や日付に変換する方法があるのでご安心下さい。
また、スキーマの自動検出機能というのはあるんですが、今回は使ってません。

これらについては後述します。

CLIによる外部テーブルの作成

準備ができたら以下のコマンドを実行しましょう。

bq mk \
  --external_table_definition=<テーブル定義ファイル名> \
  <プロジェクト名>:<データセット名>.<作成したい外部テーブルの名称>

プロジェクト名については、指定しなければCloud Shellが動いているプロジェクト名がそのまま使われますので、今回の場合は省略でいいと思います。

データセット名やテーブル名は必ず指定が必要です。例えばデータセット名が「bq_dataset」、作成したい外部テーブルの名前が「google_sheet」で準備したテーブル定義ファイルのファイル名が「definition.json」だったら、下記の様になります：

bq mk \
  --external_table_definition=./definition.json \
  bq_dataset.google_sheet

テーブル名に使える文字や文字数については、下記ドキュメントをご参照下さい。

テーブルの命名 - テーブルの作成と使用 | BigQuery | Google Cloud

問題なければ、通常は数秒でテーブルの作成は完了します。

Table '<プロジェクト名>:<データセット名>.<外部テーブル名>' successfully created.

ちなみに、Cloud Shellを開いて1回目の bq コマンドの実行の時には、権限の確認で以下のようなダイアログがでると思います。承認してあげてください。

確認と削除

CLIから確認する場合は、以下のようなSQLを叩くと良いです。

bq query \
  --nouse_legacy_sql \
  'SELECT * FROM <データセット名>.INFORMATION_SCHEMA.TABLES'

作ったばかりのテーブルの table_type に EXTERNAL と記載されてるのが分かるかと思います！

参考 : BigQueryで「テーブル」及び「ビュー」の一覧を取得する | DevelopersIO

もし何か失敗した、と思ったら、同じく bq コマンドでサクッと削除してしまいましょう。

bq rm \
  --table \
  <プロジェクト名>:<データセット名>.<作成したい外部テーブルの名称>

その他 bq コマンドで何ができるかは、下記をご参照下さい。

背景

ちなみに個人的なモチベーションというか、これで何がしたかったかというと、BQに流れ込んでくるデータとGoogleスプレッドシート上の管理台帳をIDとかで紐付けしたかったとなります。具体的には、Google Cloudの請求データをExportしているBQのテーブルと、プロジェクトIDなどに紐付けした管理者名や連絡先、作成年月なんかをJOINして、結果をさらにコネクテッドシートとしてGoogleスプレッドシートに展開、ピボットテーブルにして社内でセキュアに共有展開する、なんてことがしたかったわけです。
つか実際それに近いことが出来てます。

概要的なものを図にするとこんな感じです：

外部テーブルの作成は、コンソール（Webブラウザ）からポチポチするだけでも完了するのですが、試行錯誤中は当然のこと、実運用していく中でもけっこうな頻度で作り直しを求められることに気付きました。スキーマ情報や元スプレッドシートの値の取り込み範囲など、いちど作った外部テーブルに対してあとから修正できない項目が多いからです。

各カラムの名称と属性（文字列か数値か、NULLを許容するか）。特に名称
取り込み範囲（シート全体か一部か）

ということでいろいろ調べながら最終的には上手くいったのですが、知りたいことが全部書いてある記事というのがなかったので。。ということで出来たのがこの記事です。

参考：スキーマの自動検出を使わなかった理由

前述したみたいに、今回はスキーマの自動検出機能は使わず、テーブルスキーマを手で指定しています。理由は、今回は人間が見て人間が使うためのシート（Googleスプレッドシート）を無理矢理BigQueryに取り込んでいるからです。

外部データソースであるスプレッドシートには、必要な数値・文字列データは当然あるのですがそれがすべてではなく、その周辺には見出しだったり注釈だったり、入力データのための凡例だったりが視覚的に書き込まれているわけです。
そういったシートを「このカラムは数値」と規定してしまうと、同じカラムの下の方に「↑注意：○○の値を記入すること」みたいな注釈が書き込まれた瞬間に取り込みエラーが発生するわけです。それであればいったんは文字列・NULL許容として取り込んで SAFE_CAST() で変換する、場合によってはその前に単位やパーセント記号を RTRIM() で削除する、としたほうが取り回しやすい、と判断しました。

もちろんこれらの処理はSQLの実行時間に跳ね返る可能性があるので、データソースの規模次第であるのは間違いないところです。利便性を優先してSQL上で変換するか、実行効率を優先して元データソース（= スプレッドシート）を予め正規化しておくかは、ケースバイケースとしてご判断ください。

ちなみに概要で「さらば vlookup()」とか書いてますが本心ではないので、そこをくみ取って頂けますと幸いです。